﻿
/****** Object:  StoredProcedure [dbo].[uspPaymentPercentageBatchUpdate]    Script Date: 05/12/2013 17:31:29 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspPaymentPercentageBatchUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspPaymentPercentageBatchUpdate]
GO

-- =============================================
-- Author:		Andreas Kantounas
-- Create date: 28-Apr-2013
-- Description:	Batch Update of Payment Percentages
-- @AppliesTo:	Valid values are
--				1 : ΚΑΤΑΠΙΣΤΕΥΜΑ
--				2 : ΕΤΑΙΡΙΑ
--				3 : ΚΑΙ ΤΑ ΔΥΟ
-- =============================================
CREATE PROCEDURE [dbo].[uspPaymentPercentageBatchUpdate] 
	-- Add the parameters for the stored procedure here
	@Percentage numeric(5,2) = 0, 
	@AppliesTo int = 3
AS
BEGIN

	-- Declare Cursor Variables ========
	DECLARE @claimSerialNumber smallint
	DECLARE @katPercentage1 numeric(9,2)
	DECLARE @katPercentage2 numeric(9,2)
	DECLARE @katPercentage3 numeric(9,2)
	DECLARE @katPercentage4 numeric(9,2)
	DECLARE @katPercentage5 numeric(9,2)
	DECLARE @katAmountAccepted numeric(9,2)
	DECLARE @coPercentage1 numeric(9,2)
	DECLARE @coPercentage2 numeric(9,2)
	DECLARE @coPercentage3 numeric(9,2)
	DECLARE @coPercentage4 numeric(9,2)
	DECLARE @coPercentage5 numeric(9,2)
	DECLARE @coAmountAccepted numeric(9,2)
	
	-- Declare Cursor Variables ========
	
	DECLARE @dummy integer
	declare @currentDateTimeStamp varchar(20)
	Set @currentDateTimeStamp = convert(varchar,getdate(),120)
	Set @currentDateTimeStamp = replace(@currentDateTimeStamp,':','_')
	Set @currentDateTimeStamp = replace(@currentDateTimeStamp,' ','_')
	Set @currentDateTimeStamp = replace(@currentDateTimeStamp,'-','_')
	print @currentDateTimeStamp 
	exec('select * into CLAIMS_' + @currentDateTimeStamp + ' from CLAIMS')

	SET NOCOUNT ON

	If @Percentage = 0
		RETURN


	-- Take a backup of the table prior to updating =========================
	Set @currentDateTimeStamp = convert(varchar,getdate(),112)
	exec ('select * into CLAIMS_' + @currentDateTimeStamp + ' from CLAIMS')
	
	

	If @AppliesTo = 1			-- ΚΑΤΑΠΙΣΤΕΥΜΑ
		BEGIN
			-- Cursor Declaration =====================================================
			DECLARE claimsCursor CURSOR FOR
				SELECT	S_N,
						KAT_PERC_1, KAT_PERC_2, KAT_PERC_3, KAT_PERC_4, KAT_PERC_5, EPAL_ACC_KAT,
						CO_PERC_1, CO_PERC_2, CO_PERC_3, CO_PERC_4, CO_PERC_5, EPAL_ACC_CO
				FROM	CLAIMS
				WHERE	EPAL_EXIST = 'Ν'		-- Filter is in GREEK ("N"AI --> YES)
				AND		EPAL_ACC_KAT > 0
			-- Cursor Declaration =====================================================
		END
	Else
		If @AppliesTo = 2		-- ΕΤΑΙΡΙΑ
			BEGIN
				-- Cursor Declaration =====================================================
				DECLARE claimsCursor CURSOR FOR
					SELECT	S_N,
							KAT_PERC_1, KAT_PERC_2, KAT_PERC_3, KAT_PERC_4, KAT_PERC_5, EPAL_ACC_KAT,
							CO_PERC_1, CO_PERC_2, CO_PERC_3, CO_PERC_4, CO_PERC_5, EPAL_ACC_CO
					FROM	CLAIMS
					WHERE	EPAL_EXIST = 'Ν'		-- Filter is in GREEK ("N"AI --> YES)
					AND		EPAL_ACC_CO > 0
				-- Cursor Declaration =====================================================
			END
		Else					-- ΚΑΙ ΤΑ ΔΥΟ
			BEGIN
				-- Cursor Declaration =====================================================
				DECLARE claimsCursor CURSOR FOR
					SELECT	S_N,
							KAT_PERC_1, KAT_PERC_2, KAT_PERC_3, KAT_PERC_4, KAT_PERC_5, EPAL_ACC_KAT,
							CO_PERC_1, CO_PERC_2, CO_PERC_3, CO_PERC_4, CO_PERC_5, EPAL_ACC_CO
					FROM	CLAIMS
					WHERE	EPAL_EXIST = 'Ν'		-- Filter is in GREEK ("N"AI --> YES)
					AND		(EPAL_ACC_KAT > 0 OR EPAL_ACC_CO > 0)
				-- Cursor Declaration =====================================================
			END
	
	-- Open Cursor =====================
	OPEN claimsCursor
	FETCH NEXT FROM claimsCursor INTO
		@claimSerialNumber, 
		@katPercentage1, @katPercentage2, @katPercentage3, @katPercentage4, @katPercentage5, @katAmountAccepted,
		@coPercentage1, @coPercentage2, @coPercentage3, @coPercentage4, @coPercentage5, @coAmountAccepted
	WHILE @@FETCH_STATUS = 0
		BEGIN

			-- ==============================
			-- *** UPDATE LOGIC GOES HERE *** 
			-- ==============================
			-- Find the first non-zero percentage and update it to the desired value (@Percentage)
			-- However, we should know that there is no provision for claims that are not settled
			-- within the first 5 payments.  The database DOES NOT accommodate this case.
			
			If @AppliesTo = 1
				BEGIN
					If @katPercentage1 = 100.00 or @katPercentage2 = 100.00 or @katPercentage3 = 100.00 or @katPercentage4 = 100.00 or @katPercentage5 = 100.00
						-- DO NOTHING - Claim's ΚΑΤΑΠΙΣΤΕΥΜΑ part has been fully settled
						CONTINUE
					Else
						BEGIN
							If @katPercentage1 = 0.00
								update CLAIMS set KAT_PERC_1 = @Percentage where S_N = @claimSerialNumber
							Else
								If @katPercentage2 = 0.00
									update CLAIMS set KAT_PERC_2 = @Percentage where S_N = @claimSerialNumber
								Else
									If @katPercentage3 = 0.00
										update CLAIMS set KAT_PERC_3 = @Percentage where S_N = @claimSerialNumber
									Else
										If @katPercentage4 = 0.00
											update CLAIMS set KAT_PERC_4 = @Percentage where S_N = @claimSerialNumber
										Else
											update CLAIMS set KAT_PERC_5 = @Percentage where S_N = @claimSerialNumber
						END
				END
				
			If @AppliesTo = 2
				BEGIN
					If @coPercentage1 = 100.00 or @coPercentage2 = 100.00 or @coPercentage3 = 100.00 or @coPercentage4 = 100.00 or @coPercentage5 = 100.00
						-- DO NOTHING - Claim's ΕΤΑΙΡΙΑ part has been fully settled
						CONTINUE
					Else
						BEGIN
							If @coPercentage1 = 0.00
								update CLAIMS set CO_PERC_1 = @Percentage where S_N = @claimSerialNumber
							Else
								If @coPercentage2 = 0.00
									update CLAIMS set CO_PERC_2 = @Percentage where S_N = @claimSerialNumber
								Else
									If @coPercentage3 = 0.00
										update CLAIMS set CO_PERC_3 = @Percentage where S_N = @claimSerialNumber
									Else
										If @coPercentage4 = 0.00
											update CLAIMS set CO_PERC_4 = @Percentage where S_N = @claimSerialNumber
										Else
											update CLAIMS set CO_PERC_5 = @Percentage where S_N = @claimSerialNumber

						END

				END
				
			If @AppliesTo = 3 
				BEGIN
				-- ===================================================================================
				-- Check if the @katAmountAccepted / @coAmountAccepted is greater than zero.
				-- ONLY If YES, apply the same logic as in the @AppliesTo = 1 / @AppliesTo = 2 section
				-- ===================================================================================
				
					If @katAmountAccepted > 0.00
						BEGIN
							If @katPercentage1 = 100.00 or @katPercentage2 = 100.00 or @katPercentage3 = 100.00 or @katPercentage4 = 100.00 or @katPercentage5 = 100.00
								-- DO NOTHING - Claim's ΚΑΤΑΠΙΣΤΕΥΜΑ part has been fully settled
								set @dummy = 1
							Else
								BEGIN
									If @katPercentage1 = 0.00
										update CLAIMS set KAT_PERC_1 = @Percentage where S_N = @claimSerialNumber
									Else
										If @katPercentage2 = 0.00
											update CLAIMS set KAT_PERC_2 = @Percentage where S_N = @claimSerialNumber
										Else
											If @katPercentage3 = 0.00
												update CLAIMS set KAT_PERC_3 = @Percentage where S_N = @claimSerialNumber
											Else
												If @katPercentage4 = 0.00
													update CLAIMS set KAT_PERC_4 = @Percentage where S_N = @claimSerialNumber
												Else
													update CLAIMS set KAT_PERC_5 = @Percentage where S_N = @claimSerialNumber
								END
						END
					
					If @coAmountAccepted > 0.00
						BEGIN
							If @coPercentage1 = 100.00 or @coPercentage2 = 100.00 or @coPercentage3 = 100.00 or @coPercentage4 = 100.00 or @coPercentage5 = 100.00
								-- DO NOTHING - Claim's ΕΤΑΙΡΙΑ part has been fully settled
								set @dummy = 1
							Else
								BEGIN
									If @coPercentage1 = 0.00
										update CLAIMS set CO_PERC_1 = @Percentage where S_N = @claimSerialNumber
									Else
										If @coPercentage2 = 0.00
											update CLAIMS set CO_PERC_2 = @Percentage where S_N = @claimSerialNumber
										Else
											If @coPercentage3 = 0.00
												update CLAIMS set CO_PERC_3 = @Percentage where S_N = @claimSerialNumber
											Else
												If @coPercentage4 = 0.00
													update CLAIMS set CO_PERC_4 = @Percentage where S_N = @claimSerialNumber
												Else
													update CLAIMS set CO_PERC_5 = @Percentage where S_N = @claimSerialNumber
								END
						END
					
				END

			FETCH NEXT FROM claimsCursor INTO
				@claimSerialNumber, 
				@katPercentage1, @katPercentage2, @katPercentage3, @katPercentage4, @katPercentage5, @katAmountAccepted,
				@coPercentage1, @coPercentage2, @coPercentage3, @coPercentage4, @coPercentage5, @coAmountAccepted
		END

	-- Close & Deallocate Cursor =====================
	CLOSE claimsCursor
	DEALLOCATE claimsCursor
	-- Close & Deallocate Cursor =====================

END

GO

/****** Object:  StoredProcedure [dbo].[uspPaymentPercentageBatchUpdate]    Script Date: 05/12/2013 17:31:29 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspPaymentPercentageBatchUpdateReport]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].uspPaymentPercentageBatchUpdateReport
GO

CREATE PROCEDURE [dbo].uspPaymentPercentageBatchUpdateReport 
	-- Add the parameters for the stored procedure here
	@baktablename varchar(150) 
AS
BEGIN
declare @sqltext varchar(2000)
set @sqltext = '
select s.s_n ,

s.[KAT_PERC_1] , b.[KAT_PERC_1] as old_KAT_PERC_1 , 
s.[KAT_PERC_2] , b.[KAT_PERC_2] as old_KAT_PERC_2 , 
s.[KAT_PERC_3] , b.[KAT_PERC_3] as old_KAT_PERC_3 , 
s.[KAT_PERC_4] , b.[KAT_PERC_4] as old_KAT_PERC_4 , 
s.[KAT_PERC_5] , b.[KAT_PERC_5] as old_KAT_PERC_5 , 

s.[CO_PERC_1] , b.[CO_PERC_1] as old_CO_PERC_1 , 
s.[CO_PERC_2] , b.[CO_PERC_2] as old_CO_PERC_2 , 
s.[CO_PERC_3] , b.[CO_PERC_3] as old_CO_PERC_3 , 
s.[CO_PERC_4] , b.[CO_PERC_4] as old_CO_PERC_4 , 
s.[CO_PERC_5] , b.[CO_PERC_5] as old_CO_PERC_5 

from claims s, ' + @baktablename + ' b
where s.s_n = b.s_n
and (
	s.[KAT_PERC_1] <> b.[KAT_PERC_1] or 
	s.[KAT_PERC_2] <> b.[KAT_PERC_2] or 
	s.[KAT_PERC_3] <> b.[KAT_PERC_3] or 
	s.[KAT_PERC_4] <> b.[KAT_PERC_4] or 
	s.[KAT_PERC_5] <> b.[KAT_PERC_5] or 

	s.[CO_PERC_1] <> b.[CO_PERC_1] or 
	s.[CO_PERC_2] <> b.[CO_PERC_2] or 
	s.[CO_PERC_3] <> b.[CO_PERC_3] or 
	s.[CO_PERC_4] <> b.[CO_PERC_4] or 
	s.[CO_PERC_5] <> b.[CO_PERC_5] )'

--PRINT @sqltext
exec(@sqltext)

end

go
